﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PermitsAndVisasLeadsSyncher.CRMLeadsService;
using PermitsAndVisasLeadsSyncher.LeadsServiceXML;
using System.ServiceModel.Channels;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Net;
using System.Net.Mail;

namespace PermitsAndVisasLeadsSyncher
{
    class Program
    {

        static void Main(string[] args)
        {

            try
            {
                //MakeEmailMessageToSendToPerson("hi Rashmi");
                //Console.Read();
                PermitsAndVisasLeadsSyncher.LeadsServiceXML.pandvWSDLPortTypeClient leadsSyncherClient = new PermitsAndVisasLeadsSyncher.LeadsServiceXML.pandvWSDLPortTypeClient();
                CustomBinding binding = new CustomBinding(
                 new CustomTextMessageBindingElement("iso-8859-1", "text/xml", MessageVersion.Soap11));
                HttpTransportBindingElement httpBindingElement = new HttpTransportBindingElement();
                httpBindingElement.MaxBufferSize = Int32.MaxValue;
                httpBindingElement.MaxReceivedMessageSize = Int32.MaxValue;
                binding.Elements.Add(httpBindingElement);
                leadsSyncherClient.Endpoint.Binding = binding;
                var leadsInfo = leadsSyncherClient.getleadsinfo("pandvweb", "pandvweb2014");
                XmlDocument xmlInfo = new XmlDocument();
                xmlInfo.LoadXml(leadsInfo);
                XmlNodeList xmlnList = xmlInfo.SelectNodes("/XML_Info/data");
                LeadsInfo leadsInformation = new LeadsInfo();
                foreach (XmlNode xn in xmlnList)
                {
                    if (xn.Attributes.Count > 0)
                    {
                        if (xn.Attributes["name"].Value == "firstlead")
                        {
                            leadsInformation.FirstLeadId = xn.Attributes["id"].Value;
                            leadsInformation.FirstLeadDate = xn.Attributes["date"].Value;
                        }
                        else if (xn.Attributes["name"].Value == "lastlead")
                        {
                            leadsInformation.LastLeadId = xn.Attributes["id"].Value;
                            leadsInformation.LastLeadDate = xn.Attributes["date"].Value;
                        }
                        else if (xn.Attributes["name"].Value == "totalleads")
                        {
                            leadsInformation.LeadsCount = xn.Attributes["count"].Value;
                        }

                    }
                }               
                bool FirstLeadInfoCheck = GetFirstLeadInfoCheck();
                string FirstEdate = String.Empty;
                string lastEdate = String.Empty;
                string LastSucessfullEdate = String.Empty;
                if (FirstLeadInfoCheck == true)
                {
                    FirstEdate = leadsInformation.FirstLeadDate;
                }
                else
                {
                    FirstEdate = FetchLastSucessfullLeadEDateFromDatabase();
                }
                lastEdate = leadsInformation.LastLeadDate;
                if (FirstEdate.Trim() == lastEdate.Trim())
                {
                    Console.WriteLine("No Leads generated since last sync up with www.permitsandvisas.com");
                    return;
                }
                var result = leadsSyncherClient.getleads("pandvweb", "pandvweb2014", FirstEdate, lastEdate);
                XmlDocument xml = new XmlDocument();               
                xml.LoadXml(result);
                XmlNodeList xnList = xml.SelectNodes("/XML_Info/data");
                Console.WriteLine("Total number of leads to sync since last sync up with www.permitsandvisas.com are : {0}", xnList.Count);
                List<Lead> ListOfLeads = new List<Lead>();
                foreach (XmlNode xn in xnList)
                {
                    Lead lead = new Lead();
                    if (xn.Attributes.Count > 0)
                    {
                        lead.Id = xn.Attributes["id"].Value;
                        lead.Name = xn.Attributes["name"].Value;
                        lead.Email = xn.Attributes["email"].Value;
                        lead.MobileNumber = xn.Attributes["mobno"].Value;
                        lead.PhoneNumber = xn.Attributes["phone"].Value;
                        lead.Message = xn.Attributes["message"].Value;
                        lead.DateOfBirth = xn.Attributes["dob"].Value;
                        lead.Nationality = xn.Attributes["nationality"].Value;
                        lead.Education = xn.Attributes["education"].Value;
                        lead.VisaType = xn.Attributes["visatype"].Value;
                        lead.Occupation = xn.Attributes["occupation"].Value;
                        lead.NoOfExperience = xn.Attributes["noofexp"].Value;
                        lead.sorceCountry = xn.Attributes["src_country"].Value;
                        lead.DestinationCountry = xn.Attributes["dest_country"].Value;
                        lead.LeadSource = xn.Attributes["leadsrc"].Value;
                        lead.EnteredDate = xn.Attributes["edate"].Value;
                    }
                    ListOfLeads.Add(lead);

                }
                bool IsLeadSuccessfullyEntered = false;
                List<Country> countries = new List<Country>();
                string fetchCountryListQuery = MakeSQLQueryToFetchCountryList();                
                FillListOfCountriesPresentInCRMDatabase(countries, fetchCountryListQuery);
                List<ProductInfo> Products = new List<ProductInfo>();
                string FetchProductsListQuery = MakeSqlQuerytoReturnProductInfo();
                FillListOfProductPresentInDatabase(Products, FetchProductsListQuery);
                foreach (Lead Leads in ListOfLeads)
                {

                    IsLeadSuccessfullyEntered = InsertLeadIntoCRMDatabase(Leads , countries,Products );
                    if (IsLeadSuccessfullyEntered == true)
                    {
                        LastSucessfullEdate = Leads.EnteredDate;
                    }
                }

                UpdateLeadsServiceInfoIntodatabase(LastSucessfullEdate);
                //MakeEmailMessageToSendToPerson("hi Rashmi");
            }

            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("Leads synced up successfully !");
            Console.Read();
        }

        private static void FillListOfProductPresentInDatabase(List<ProductInfo> Products, string FetchProductsListQuery)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["dbconnstring"].ConnectionString;
            SqlConnection SQLConn = new SqlConnection(connectionString);
            DataTable productList = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();

            try
            {
                //connection opened  
                SQLConn.Open();
                //command created
                SqlCommand command = SQLConn.CreateCommand();
                command.CommandText = FetchProductsListQuery; adapter.SelectCommand = command;
                adapter.Fill(productList);

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (SQLConn != null)
                {
                    SQLConn.Close();
                }
            }

            if (productList.Rows.Count > 0)
            {
                foreach (DataRow currentProduct in productList.Rows)
                {
                    ProductInfo Product = new ProductInfo();
                    Product.ProductId = currentProduct["Product_ID"].ToString();
                    Product.ProductName = currentProduct["Product_Name"].ToString();
                    Product.ProductTypeName = currentProduct["ProductType_Name"].ToString();
                    Product.ProductTypeResidentId = currentProduct["ProductType_ResidenceID"].ToString();
                    Product.ProductDestinationId = currentProduct["Product_DestinationID"].ToString();
                    Products.Add(Product);
                }
            }
        }

        private static string FetchLastSucessfullLeadEDateFromDatabase()
        {
            string result = string.Empty;
            string connectionString = ConfigurationManager.ConnectionStrings["dbconnstring"].ConnectionString;
            SqlConnection SQLConn = new SqlConnection(connectionString);
            try
            {
                //connection opened  
                SQLConn.Open();
                //command created
                SqlCommand command = SQLConn.CreateCommand();
                command.CommandText = MakeSqlQueryToFetchLastSucessfullLeadSynched();
                object obj = command.ExecuteScalar();
                result = obj.ToString();

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (SQLConn != null)
                {
                    SQLConn.Close();
                }
            }
            return result;
        }

        private static string MakeSqlQuerytoReturnProductInfo()
        {
            StringBuilder SqlQuery = new StringBuilder();
            SqlQuery.Append("SELECT prod.Product_ID,prod.Product_Name,ptype.ProductType_Name, prod.Product_DestinationID, ");
            SqlQuery.Append("ptype.ProductType_ResidenceID from L_Products as Prod join L_ProductTypes as ptype");
            SqlQuery.Append(" on prod.Product_Type=ptype.ProductType_ID");
            return SqlQuery.ToString();
        }

        private static string MakeSqlQueryToFetchLastSucessfullLeadSynched()
        {
            StringBuilder SqlQuery = new StringBuilder();
            SqlQuery.Append("declare  @LastId int ");
            SqlQuery.Append("set @LastId =(select MAX(Id) FROM [Global].[dbo].[L_LeadsSyncInfo]  where [LastSuccessfullLeadDate] not like '' ) ");
            SqlQuery.Append("SELECT [LastSuccessfullLeadDate] FROM [Global].[dbo].[L_LeadsSyncInfo] where ID= @LastId  ");
            return SqlQuery.ToString();
        }

        private static void UpdateLeadsServiceInfoIntodatabase(string LastSucessfullEdate)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["dbconnstring"].ConnectionString;
            SqlConnection SQLConn = new SqlConnection(connectionString);
            try
            {
                //connection opened  
                SQLConn.Open();
                //command created
                SqlCommand command = SQLConn.CreateCommand();
                command.CommandText = MakeSQLQueryToAddLastSuccessfullLeadsDate(LastSucessfullEdate);
                object obj = command.ExecuteNonQuery();

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (SQLConn != null)
                {
                    SQLConn.Close();
                }
            }
        }

        private static string MakeSQLQueryToAddLastSuccessfullLeadsDate(string LastSucessfullEdate)
        {
            string query = "INSERT INTO [Global].[dbo].[L_LeadsSyncInfo] ([LastSuccessfullLeadDate],[LeadSyncTime]) values ('" + LastSucessfullEdate + "','" + DateTime.Now.ToString() + "')";
            return query;
        }

        private static bool GetFirstLeadInfoCheck()
        {
            bool result = false;
            string connectionString = ConfigurationManager.ConnectionStrings["dbconnstring"].ConnectionString;
            SqlConnection SQLConn = new SqlConnection(connectionString);
            try
            {
                //connection opened  
                SQLConn.Open();
                //command created
                SqlCommand command = SQLConn.CreateCommand();
                command.CommandText = MakeSqlQueryToGetFirstLeadInfoCheckValue();
                object obj = command.ExecuteScalar();
                if (obj != null)
                {
                    int Id = Convert.ToInt32(obj);                    
                }

            }
            catch (InvalidCastException ex)
            {
                result = true;
            }
            catch (Exception ex)
            {

                throw;
            }
            finally
            {
                if (SQLConn != null)
                {
                    SQLConn.Close();
                }
            }
            return result;
        }

        private static string MakeSqlQueryToGetFirstLeadInfoCheckValue()
        {
            string query = "SELECT MAX(Id) FROM [Global].[dbo].[L_LeadsSyncInfo]";
            return query;
        }

        private static bool InsertLeadIntoCRMDatabase(Lead Leads , List<Country> countries, List<ProductInfo> products)
        {
            bool IsLeadSuccessfullyEntered = false;
            string connectionString = ConfigurationManager.ConnectionStrings["dbconnstring"].ConnectionString;
            SqlConnection SQLConn = new SqlConnection(connectionString);
            SqlTransaction sqlTran = null;
            try
            {
                //connection opened  
                SQLConn.Open();
                // Start a local transaction.
                sqlTran = SQLConn.BeginTransaction();
                // Enlist a command in the current transaction.
                SqlCommand command = SQLConn.CreateCommand();
                command.Transaction = sqlTran;                
                //check the current leads qualification in master database if not present then insert the same
                List<Education> EducationList = new List<Education>();
                string fetchEducationListQuery = MakeSQLQueryToFetchEducationList();
                command.CommandText = fetchEducationListQuery;
                FillEducationList(EducationList, command);
                CheckTheLeadsQualificationAndUpdateDatabaseIfNeeded(EducationList, Leads, command);
                //"500019";
                Leads.NationalityID = ReturnCountryId(countries, Leads.Nationality);
                Leads.sorceCountryID = ReturnCountryId(countries, Leads.sorceCountry);
                Leads.DestinationCountryId = ReturnCountryId(countries, Leads.DestinationCountry);
                string addPersonQuery = MakeSQLQueryToAddPerson(Leads);
                string personId = AddPersonToCRMDataBaseAndReturnPersonId(addPersonQuery, command);
                Leads.PersonID = personId;
                if (!string.IsNullOrEmpty(Leads.PersonID))
                {
                    string addNewEventOfAddingPerson = MakeSQLQueryToAddNewEventOfAddingPerson(Leads);
                    AddNewEventToCRMDatabaseAndReturn(addNewEventOfAddingPerson, command);
                }
                
                if (!string.IsNullOrEmpty(Leads.Email.Trim()))
                {
                    string addEmailAddressQuery = MakeSQLQueryToAddEmailAddress(Leads);
                    AddPersonEmailAddress(addEmailAddressQuery, command);
                }
                if (!string.IsNullOrEmpty(Leads.PhoneNumber.Trim()))
                {
                    string addPhoneQuery = MakeSQLQueryToAddPhoneNumber(Leads);
                    AddPhoneNumberOfPerson(addPhoneQuery, command);
                }
                if (!string.IsNullOrEmpty(Leads.MobileNumber.Trim ()))
                {
                    string addMobileNumberQuery = MakeSQLQueryToAddMobileNumber(Leads);
                    AddMobileNumberOfPerson(addMobileNumberQuery, command);
                }
                string productId = SearchProductIdForCurrentlead(Leads, products);
                Leads.ProductId = productId;
                string addNewCaseQuery = MakeSQLQueryToAddNewCase(Leads);
                string caseId=AddnewCasetoCRMDatabaseAndReturnCaseId(addNewCaseQuery, command); 
                Leads.CaseId = caseId;
                if (!string.IsNullOrEmpty(Leads.CaseId))
                {
                    string addNewEventQueryOfAddingCase = MakeSQLQueryToAddNewEventOfAddingCase(Leads);
                    AddNewEventToCRMDatabaseAndReturn(addNewEventQueryOfAddingCase, command);
                }
                
                if (!string.IsNullOrEmpty(Leads.Message .Trim()))
                {
                    string addWebsiteCommentQuery = MakeSQLQueryToAddWebsiteComment(Leads);
                    AddWebsiteComment(addWebsiteCommentQuery, command);
                }                
                IsLeadSuccessfullyEntered = true;
            }
            catch (Exception ex)
            {
                // Handle the exception if the transaction fails to commit.
                Console.WriteLine(ex.Message);
                try
                {
                    // Attempt to roll back the transaction.
                    sqlTran.Rollback();
                }
                catch (Exception exRollback)
                {
                    // Throws an InvalidOperationException if the connection  
                    // is closed or the transaction has already been rolled  
                    // back on the server.
                    Console.WriteLine(exRollback.Message);
                }
                sqlTran = null;
            }
            finally
            {
                if (sqlTran != null)
                {
                    sqlTran.Commit();
                }
                SQLConn.Close();
            }
            return IsLeadSuccessfullyEntered;
        }

        private static void AddNewEventToCRMDatabaseAndReturn(string addNewEventQuery, SqlCommand command)
        {
            command.CommandText = addNewEventQuery;
            command.ExecuteNonQuery();            
        }        

        private static string MakeSQLQueryToAddNewEventOfAddingPerson(Lead Leads)
        {
            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.Append("INSERT INTO D_Event ( Event_Description, Event_Message, Event_EnteredBy, Event_DTEntered, Event_Category, Event_LinkID,");
            sqlQuery.Append("Event_LinkType, Event_Keydate, Event_UserEvent)");
            sqlQuery.Append(" VALUES ( 'Person Added', '"+Leads.Name +"', 0, '"+Leads.EnteredDate +"', 1, '" +Leads.PersonID +"', 2 , 0,0 ) ");
            return sqlQuery.ToString();
        }

        private static string MakeSQLQueryToAddNewEventOfAddingCase(Lead Leads)
        {
            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.Append("INSERT INTO D_Event ( Event_Description, Event_Message, Event_EnteredBy, Event_DTEntered, Event_Category, Event_LinkID,");
            sqlQuery.Append("Event_LinkType, Event_Keydate, Event_UserEvent)");
            sqlQuery.Append(" VALUES ( 'Case Added', '" + Leads.Name + "', 0, '" + Leads.EnteredDate + "', 1, '" + Leads.CaseId + "', 1 , 0,0 ) ");
            return sqlQuery.ToString();
        }

        private static string SearchProductIdForCurrentlead(Lead Leads, List<ProductInfo> products)
        {
            string productId = string.Empty;
            foreach (ProductInfo currentproduct in products)
            {
                if (Leads.DestinationCountryId == currentproduct.ProductDestinationId)
                {
                    productId = currentproduct.ProductId;
                    break;
                }
            }
            if(string.IsNullOrEmpty ( productId ))
            {
                ProductInfo defaultProduct = products.Find(x=> x.ProductTypeName.ToUpper()=="UAE-WWW");
                productId = defaultProduct.ProductId;
            }
            return productId;
        }

        private static string AddnewCasetoCRMDatabaseAndReturnCaseId(string addNewCaseQuery, SqlCommand command)
        {
            command.CommandText = addNewCaseQuery;
            Int32 result = (Int32)command.ExecuteScalar();
            return result.ToString();
        }

        private static string MakeSQLQueryToAddNewCase(Lead Leads)
        {            
            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.Append("Insert Into D_Case (Case_DtEntered,Case_EnteredBy, Case_ProductID, Case_ConSaleID, Case_ConCWID ");
            sqlQuery.Append(" , Case_CompanyID, Case_StatusID, Case_DTStatus, Case_DTsold, Case_CandidateID, Case_ContactID, Case_PrimaryCaseID, Case_CoordinatorID)");
            sqlQuery.AppendLine(" OUTPUT INSERTED.Case_ID ");
            sqlQuery.Append(" values ('" + Leads.EnteredDate + "','LeadSyncher','" + Leads.ProductId + "',0,0,0,1,'" + Leads.EnteredDate + "','" + Leads.EnteredDate + "','" + Leads.PersonID + "',0,0,0)");
            return sqlQuery.ToString();
        }

        private static void CheckTheLeadsQualificationAndUpdateDatabaseIfNeeded(List<Education> EducationList, Lead Leads, SqlCommand command)
        {
            bool isEducationInDb = false;
            if (!string.IsNullOrEmpty(Leads.Education))
            {
                foreach (Education education in EducationList)
                {
                    if (education.Name == Leads.Education)
                    {
                        isEducationInDb = true;
                        break;
                    }
                }
                if (!isEducationInDb)
                {
                    command.CommandText = "insert into [GLOBAL].[dbo].[L_Qualifications] ([Qual_Name] ) values ('" + Leads.Education + "')";
                    command.ExecuteNonQuery();
                }
            }
        }

        private static string MakeSQLQueryToFetchEducationList()
        {
            string query = "SELECT [Qual_Name] FROM [GLOBAL].[dbo].[L_Qualifications]";
            return query;
        }

        private static string MakeSQLQueryToFetchCountryList()
        {
            string query = "SELECT [Country_ID],[Country_Name],[Country_Nationality]  FROM [GLOBAL].[dbo].[L_Country]";
            return query;
        }

        private static void AddMobileNumberOfPerson(string addMobileNumberQuery, SqlCommand command)
        {
            command.CommandText = addMobileNumberQuery;
            command.ExecuteNonQuery();
        }

        private static void AddPhoneNumberOfPerson(string addPhoneQuery, SqlCommand command)
        {
            command.CommandText = addPhoneQuery;
            command.ExecuteNonQuery();
        }

        private static void AddPersonEmailAddress(string addEmailAddressQuery, SqlCommand command)
        {
            command.CommandText = addEmailAddressQuery;
            command.ExecuteNonQuery();
        }

        private static void AddWebsiteComment(string addWebsiteComment, SqlCommand command)
        {
            command.CommandText = addWebsiteComment;
            command.ExecuteNonQuery();
        }

        private static string ReturnCountryId(List<Country> countries, string countryName)
        {
            string countryId = string.Empty;
            foreach (Country country in countries)
            {
                if (country.Name == countryName)
                {
                    countryId = country.Id;
                    break;
                }
            }
            return countryId;
        }

        private static string AddPersonToCRMDataBaseAndReturnPersonId(string addPersonQuery, SqlCommand command)
        {
            command.CommandText = addPersonQuery;
            Int32 result = (Int32)command.ExecuteScalar();
            return result.ToString();
        }

        private static void FillEducationList(List<Education> educationList, SqlCommand command)
        {
            DataTable EducationList = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(EducationList);
            if (EducationList.Rows.Count > 0)
            {
                foreach (DataRow currentRow in EducationList.Rows)
                {
                    educationList.Add(new Education() { Name = currentRow["Qual_Name"].ToString() });
                }
            }
        }

        private static void FillListOfCountriesPresentInCRMDatabase(List<Country> countries, string  query)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["dbconnstring"].ConnectionString;
            SqlConnection SQLConn = new SqlConnection(connectionString);
            DataTable countryList = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();
            
            try
            {
                //connection opened  
                SQLConn.Open();
                //command created
                SqlCommand command = SQLConn.CreateCommand();
                command.CommandText = query; adapter.SelectCommand = command;
                adapter.Fill(countryList);

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (SQLConn != null)
                {
                    SQLConn.Close();
                }
            }
            
            if (countryList.Rows.Count > 0)
            {
                foreach (DataRow currentCountry in countryList.Rows)
                {
                    Country country = new Country();
                    country.Id = currentCountry["Country_ID"].ToString();
                    country.Name = currentCountry["Country_Name"].ToString();
                    countries.Add(country);
                }
            }
        }

        private static string MakeSQLQueryToAddMobileNumber(Lead Leads)
        {
            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.Append("insert into [GLOBAL].[dbo].[D_PhoneNumber] ( [PhoneNumber_Name] ");
            sqlQuery.Append(",[PhoneNumber_Number],[PhoneNumber_Type] ,[PhoneNumber_PersonID],[PhoneNumber_DTEntered]");
            sqlQuery.Append(",[PhoneNumber_EnteredBy] ) ");
            sqlQuery.Append("values ('Mobile', '" + Leads.MobileNumber + "', 1 ," + Leads.PersonID + ", '" + Leads.EnteredDate + "' ,'' ) ");
            return sqlQuery.ToString();
        }

        private static string MakeSQLQueryToAddPhoneNumber(Lead Leads)
        {

            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.Append("insert into [GLOBAL].[dbo].[D_PhoneNumber] ( [PhoneNumber_Name] ");
            sqlQuery.Append(",[PhoneNumber_Number],[PhoneNumber_Type] ,[PhoneNumber_PersonID],[PhoneNumber_DTEntered]");
            sqlQuery.Append(",[PhoneNumber_EnteredBy] ) ");
            sqlQuery.Append("values ('Phone', '" + Leads.PhoneNumber + "', 1 ," + Leads.PersonID + ", '" + Leads.EnteredDate + "' ,'' ) ");
            return sqlQuery.ToString();
        }

        private static string MakeSQLQueryToAddEmailAddress(Lead Leads)
        {
            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.Append("insert into [GLOBAL].[dbo].[D_EmailAddress] ( [EMailAddress_Name] ");
            sqlQuery.Append(",[EMailAddress_Address],[EMailAddress_PersonID] ,[EMailAddress_Type],[EmailAddress_DTEntered]");
            sqlQuery.Append(",[EMailAddress_EnteredBy] ) ");
            sqlQuery.Append("values ('Email', '" + Leads.Email + "'," + Leads.PersonID + "," + '1' + ",'" + Leads.EnteredDate + "' ,'' ) ");
            return sqlQuery.ToString();
        }

        private static string MakeSQLQueryToAddWebsiteComment(Lead Leads)
        {
            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.Append("INSERT INTO D_Event ( Event_Description, Event_Message, Event_EnteredBy, Event_DTEntered, Event_Category, Event_LinkID, ");
            sqlQuery .Append (" Event_LinkType, Event_Keydate, Event_UserEvent)");            
            sqlQuery.Append("VALUES ( 'Website Comments', '" + Leads .Message + "', 0, '"+Leads .EnteredDate +"', 1, '"+Leads.CaseId+"', 1 , 0,1 )");
            return sqlQuery.ToString();
        }
        
        private static string MakeSQLQueryToAddPerson(Lead Leads)
        {
            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.Append("insert into [GLOBAL].[dbo].[D_Person] ( [Person_FName] ");
            sqlQuery.Append(",[Person_EnteredBy],[Person_DTEntered] ,[Person_Lname],[Person_Title]");
            sqlQuery.Append(",[Person_NatID],[Person_ResidentID],[Person_UKVisaID],[Person_DestinationID] ");
            sqlQuery.Append(",[Person_Jobtitle],[Person_salary],[Person_Qualification],[Person_PassportNo] ");
            sqlQuery.Append(",[person_dob],[person_expirydt],[person_PassportDTexpiry],[Person_CompanyID]");
            sqlQuery.Append(",[Person_CompanyRoleID],[Person_AccountManager])");
            sqlQuery.AppendLine(" OUTPUT INSERTED.Person_ID ");
            sqlQuery.Append(" values ('" + Leads.Name + "',0,'" + Leads.EnteredDate + "','','','" + Leads.NationalityID + "','" + Leads.sorceCountryID + "',8,'" + Leads.DestinationCountryId);
            sqlQuery.Append("','"+Leads.Occupation+"','','" + Leads.Education + "','',null,null,null,0,0,0)");
            return sqlQuery.ToString();
        }

        private static void MakeEmailMessageToSendToPerson(string message)
        {
            try
            {
                MailMessage Email= new MailMessage();
                Email.To.Add("kumarkisna@gmail.com");
                Email.Subject = "This is the Subject line";
                Email.From = new System.Net.Mail.MailAddress("leadsyncher@permitsandvisas.com");
                NetworkCredential cred = new NetworkCredential("razim@permitsandvisas.com", "Test123");
                SmtpClient client = new SmtpClient();
                client.Port = 3535;
                client.Credentials = cred;
                //client.EnableSsl = true;
                client.UseDefaultCredentials = false;

                client.Host = "smtpout.secureserver.net";
                Email.Subject = "this is a test email.";
                Email.Body = message;
                client.Send(Email);

            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message.ToString());
            }
        }
    }
}
